package chen.web.favorite;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;

import chen.web.DataSourceProvider;
import chen.web.MyContextProvider;
import chen.web.user.Account;
import chen.web.user.AccountManager;
import chen.web.util.DateInt;

public class FavoriteManagerImpl implements FavoriteManager{

	private static SimpleJdbcTemplate simpleJdbcTemplate = new SimpleJdbcTemplate(DataSourceProvider.getDataSource());
	
	private Showable[] showableArray;
	private AccountManager am;
	
	public FavoriteManagerImpl(AccountManager am, Showable[] showableArray){
		this.am = am;
		this.showableArray = showableArray;
	}
	
	private static final String SQL_1 = "insert into web_favorite(fav_title,fav_description,fav_uri,fav_date,fav_ownerId,fav_type,fav_flag) values(?,?,?,?,?,?,?)";
	@Override
	public void add(Favoritable favoritable) {
		// TODO Auto-generated method stub
		Favorite fav = favoritable.favor();
		for(Showable showable : showableArray){
			if(showable.support(favoritable)){
				fav.setUri(showable.showURL(favoritable));
			}
		}
		if(fav.getOwner() == null){
			fav.setOwner(MyContextProvider.getMyContext().getAccount());
		}
		simpleJdbcTemplate.update(SQL_1, fav.getTitle(),fav.getDescription(),fav.getUri(),DateInt.transform(fav.getDate()),fav.getOwner().getId(), fav.getType(), fav.getFlag());
	}

	private static final String SQL_2 = "select count(*) from web_favorite where fav_ownerId=? and fav_type=?";
	@Override
	public int amount(Account owner, Class<?> clazz) {
		// TODO Auto-generated method stub
		String type = Favorite.getType(clazz);
		return simpleJdbcTemplate.queryForInt(SQL_2, owner.getId(), type);
	}

	private static final String SQL_3 = "select count(*) from web_favorite where fav_ownerId = ?";
	@Override
	public int amount(Account owner) {
		// TODO Auto-generated method stub
		return simpleJdbcTemplate.queryForInt(SQL_3, owner.getId());
	}

	private static final String SQL_4 = "delete from web_favorite where fav_id = ?";
	@Override
	public void delete(Favorite favorite) {
		// TODO Auto-generated method stub
		simpleJdbcTemplate.update(SQL_4, favorite.getId());
	}

	private static final String SQL_5 = "select * from web_favorite where fav_id = ?";
	@Override
	public Favorite getFavorite(int id) {
		// TODO Auto-generated method stub
		Favorite fav = null;
		try{
			fav = simpleJdbcTemplate.queryForObject(SQL_5, new FavoriteRowMapper(), id);	
		} catch(EmptyResultDataAccessException e){
			return null;
		}
		int ownerId = fav.getOwner().getId();
		Account owner = am.getAccount(ownerId);
		fav.setOwner(owner);
		return fav;
	}

	private static final String SQL_7 = "select * from web_favorite where fav_ownerId = ? and fav_type = ? order by fav_id desc limit ?,?";
	@Override
	public List<Favorite> listFavorite(Account owner, Class<?> type, int beginOffset, int count) {
		// TODO Auto-generated method stub
		List<Favorite> list = simpleJdbcTemplate.query(SQL_7, new FavoriteRowMapper(), owner.getId(), Favorite.getType(type), beginOffset, count);
		for(Favorite fav : list){
			fav.setOwner(owner);
		}
		return list;
	}

	private static final String SQL_8 = "select * from web_favorite where fav_ownerId = ? order by fav_id desc limit ?,?";
	@Override
	public List<Favorite> listFavorite(Account owner, int beginOffset, int count) {
		// TODO Auto-generated method stub
		List<Favorite> list = simpleJdbcTemplate.query(SQL_8, new FavoriteRowMapper(), owner.getId(), beginOffset, count);
		for(Favorite fav : list){
			fav.setOwner(owner);
		}
		return list;
	}

	private static class FavoriteRowMapper implements RowMapper<Favorite>{

		@Override
		public Favorite mapRow(ResultSet rs, int rowNum) throws SQLException {
			// TODO Auto-generated method stub
			int id = rs.getInt("fav_id");
			String title = rs.getString("fav_title");
			String description = rs.getString("fav_description");
			String uri = rs.getString("fav_uri");
			Date date = DateInt.transform(rs.getInt("fav_date"));
			int ownerId = rs.getInt("fav_ownerId");
			String type = rs.getString("fav_type");
			int flag = rs.getInt("fav_flag");
			
			Account owner = new Account(ownerId);
			Favorite fav = new Favorite();
			fav.setId(id);
			fav.setTitle(title);
			fav.setFlag(flag);
			fav.setDescription(description);
			fav.setUri(uri);
			fav.setOwner(owner);
			fav.setType(type);
			fav.setDate(date);
			return fav;
		}
		
	}
}
